﻿Imports QLTHUVIEN.DTO
Public Class SachDAO
    Public Function layDanhSachSach() As DataTable
        Dim strSql = "SELECT S.MaSach, S.TenSach, S.TacGia, S.NamXuatBan, S.NgayNhap, S.TriGia, TL.TenTheLoai, NXB.TenNhaXuatBan, NV.TenNhanVien FROM THONGTINSACH S, THELOAISACH TL, NHAXUATBAN NXB, NHANVIEN NV WHERE S.MaTheLoai = TL.MaTheLoai AND S.MaNhaXuatBan = NXB.MaNhaXuatBan AND S.MaNguoiTiepNhan = NV.MaNhanVien AND S.isDelete = 'False'"
        Dim dt As DataTable = DataProvider.ExecuteQuery(strSql)
        Return dt
    End Function

    Public Sub themSach(ByVal sach As SachDTO)
        Dim strSql As String = "INSERT THONGTINSACH(TenSach, TacGia, NamXuatBan, NgayNhap, TriGia, MaTheLoai, MaNhaXuatBan, MaNguoiTiepNhan) VALUES(N'" & sach.TenSach & "', N'" & sach.TacGia & "', " & sach.NamXuatBan & ", '" & sach.NgayNhap & "', " & sach.TriGia & ", " & sach.MaTheLoai & ", " & sach.MaNhaXuatBan & ", " & sach.MaNhanVien & ")"
        DataProvider.ExecuteQuery(strSql)
    End Sub

    Public Sub suaSach(ByVal sach As SachDTO)
        Dim strSql As String = "UPDATE THONGTINSACH SET TenSach = N'" & sach.TenSach & "', TacGia =N'" & sach.TacGia & "', NamXuatBan= " & sach.NamXuatBan & ", NgayNhap = '" & sach.NgayNhap & "', TriGia = " & sach.TriGia & ", MaTheLoai = " & sach.MaTheLoai & ", MaNhaXuatBan = " & sach.MaNhaXuatBan & ", MaNguoiTiepNhan = " & sach.MaNhanVien & " WHERE MaSach = " & sach.MaSach
        DataProvider.ExecuteQuery(strSql)
    End Sub

    ''xoa sach 
    Public Sub xoaSach(ByVal sach As SachDTO)
        Dim strSql As String = "UPDATE THONGTINSACH SET isDelete ='" & sach.IsDelete.ToString() & "' WHERE MaSach = " & sach.MaSach
        DataProvider.ExecuteQuery(strSql)
    End Sub

    Public Function TimKiemTheoMaSach(ByVal masach As Integer) As DataTable
        Dim strSql As String = "SELECT * FROM ThongTinSach WHERE MaSach =" & masach
        Return DataProvider.ExecuteQuery(strSql)
    End Function

    Public Function TimKiemTheoTenSach(ByVal tensach As String) As DataTable
        Dim strSQL As String = "Select * from ThongTinSach where TenSach = N" & "'" & tensach & "'"
        Return DataProvider.ExecuteQuery(strSQL)
    End Function

    Public Function TimKiemTheoNamXuatBan(ByVal namxuatban As Integer) As DataTable
        Dim strSQL As String = "Select * from ThongTinSach where NamXuatBan =" & namxuatban
        Return DataProvider.ExecuteQuery(strSQL)
    End Function

    Public Function TimKiemTheoNhaXuatBan(ByVal nhaxuatban As String) As DataTable
        Dim strSQL As String = "Select * from ThongTinSach tts,NhaXuatBan nxb where tts.MaNhaXuatBan = nxb.MaNhaXuatBan and nxb.TenNhaXuatBan=N" & "'" & nhaxuatban & "'"
        Return DataProvider.ExecuteQuery(strSQL)
    End Function

    Public Function TimKiemTheoTheLoai(ByVal theloai As String) As DataTable
        Dim strSQL As String = "Select * from ThongTinSach tts,TheLoaiSach tls where tts.MaTheLoai = tls.MaTheLoai and tls.TenTheLoai =N" & "'" & theloai & "'"
        Return DataProvider.ExecuteQuery(strSQL)
    End Function
End Class
